package com.yayan.beautyclub.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.mysql.jdbc.Statement;
import com.yayan.beautyclub.bean.Product;

public class ProductDao extends ConnectionBase implements IProductDao {

	public ProductDao(){}
	
	@Override
	public Map<String, Object> findProductBypId(long pId) throws Exception {
		// TODO Auto-generated method stub
		QueryRunner qRunner = new QueryRunner();
		String queryStr = "select pId,pName,titlePic,price,description,products.cId as cId,parentId,cName,images,currentPrice,sales"
				+ " from products,category where products.cId=category.cId and pId=? and products.status=1 and category.status=1";
		Connection conn = getConn();
		Map<String, Object> map = qRunner.query(conn, queryStr, new MapHandler(), pId);
		return map;
	}

	@Override
	public List<Product> findProductBycId(int cId, int page, int size) throws Exception {
		// TODO Auto-generated method stub
		QueryRunner qRunner = new QueryRunner();
		String queryStr = "select * from products,category where products.cId=? and products.cId=category.cId";
		queryStr += " and products.status=1 and category.status=1 limit " + (page-1)*size + "," + size;
		String sqlNum = "select COUNT(*) as status from products,category where products.cId=?";
		sqlNum += " and products.cId=category.cId and products.status=1 and category.status=1";
		Connection conn = getConn();
		Product product = (Product)qRunner.query(conn, sqlNum, new BeanHandler(Product.class), cId);
		if(product != null)
			product.setStatus(product.getStatus()/size + (product.getStatus()%size>0?1:0));
		List<Product> list = (List<Product>) qRunner.query(conn, queryStr, new BeanListHandler(Product.class), cId);
		list.add(0,product);
		return list;
	}
	
	@Override
	public List<Product> findProductByParentId(int parentId, int page, int size) throws Exception {
		// TODO Auto-generated method stub
		QueryRunner qRunner = new QueryRunner();
		String queryStr = "select * from products,category where products.cId=category.cId and category.parentId=?";
		queryStr += " and products.status=1 and category.status=1 limit " + (page-1)*size + "," + size;
		String sqlNum = "select COUNT(*) as status from products,category where products.cId=category.cId";
		sqlNum += " and category.parentId=? and products.status=1 and category.status=1";
		Connection conn = getConn();
		Product product = (Product)qRunner.query(conn, sqlNum, new BeanHandler(Product.class), parentId);
		if(product != null)
			product.setStatus(product.getStatus()/size + (product.getStatus()%size>0?1:0));
		List<Product> list = (List<Product>) qRunner.query(conn, queryStr, new BeanListHandler(Product.class), parentId);
		list.add(0,product);
		return list;
	}
	
	@Override
	public List<Product> findProductByActivity() throws Exception {
		// TODO Auto-generated method stub
		QueryRunner qRunner = new QueryRunner();
		String queryStr = "select pId,titlePic,pName,price,currentPrice from products where pId in (1,3,5,7,9) and status=1";
		List<Product> list = (List<Product>) qRunner.query(getConn(), queryStr, new BeanListHandler(Product.class));
		return list;
	}

	@Override
	public long save(Product product) throws Exception {
		// TODO Auto-generated method stub
		String sql="insert into products(pName,price,cId,images,time,currentPrice,sales,titlePic)"
				+ "  values(?,?,?,?,?,?,?,?);";
		Connection conn = getConn();
		conn.setAutoCommit(false);       //不自动提交  
		PreparedStatement ps= conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
		ps.setString(1, product.getpName());
		ps.setFloat(2, product.getPrice());		
		ps.setInt(3, product.getcId());
		ps.setString(4, product.getImages());		
		ps.setString(5, new java.sql.Timestamp(product.getTime().getTime()).toString());
		ps.setFloat(6, product.getCurrentPrice());
		ps.setInt(7, product.getSales());
		ps.setString(8, product.getTitlePic());
		int re = ps.executeUpdate();
		ResultSet rsKey = ps.getGeneratedKeys(); 
		long r=0;
		while(re!=0&&rsKey.next())
		{
			r = rsKey.getLong(1);			
		}
		conn.commit();
		conn.setAutoCommit(true);//设回自动提交
		close(conn,ps,rsKey);
		return r;
	}

	@Override
	public void save(String... values) throws Exception {
		// TODO Auto-generated method stub
		
	}

	@Override
	public boolean update(Product product) throws Exception {
		// TODO Auto-generated method stub
		String sql = "update products set pName=?,price=?,cId=?,images=?,time=?,currentPrice=?,sales=?,titlePic=? where pId=?";
		Connection conn = getConn();
		PreparedStatement ps= conn.prepareStatement(sql);
		ps.setString(1, product.getpName());
		ps.setFloat(2, product.getPrice());		
		ps.setInt(3, product.getcId());
		ps.setString(4, product.getImages());
		ps.setString(5, new java.sql.Timestamp(product.getTime().getTime()).toString());
		ps.setFloat(6, product.getCurrentPrice());
		ps.setInt(7, product.getSales());
		ps.setString(8, product.getTitlePic());
		ps.setLong(9, product.getpId());		
		boolean r = ps.executeUpdate() > 0 ? true:false;
		close(conn,ps,null);
		return r;
	}

	@Override
	public boolean delete(long pId) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr = "update products set status=0 where pId=?";
		Connection conn = getConn();
		PreparedStatement ps = conn.prepareStatement(sqlStr);
		ps.setLong(1, pId);
		boolean r = ps.executeUpdate() > 0 ? true : false;
		close(conn,ps,null);
		return r;
	}

	@Override
	public boolean addSales(long pId) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr = "update products set sales=sales+1 where pId=?";
		Connection conn = getConn();
		PreparedStatement ps = conn.prepareStatement(sqlStr);
		ps.setLong(1, pId);
		boolean r = ps.executeUpdate() > 0 ? true : false;
		close(conn,ps,null);
		return r;
	}

	@Override
	public List<Map<String, Object>> findProductByCondition(int page, int size, int firstcId, int secondcId,
			String searchKey) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr = "select pId,pName,titlePic,description,products.time as time,price,currentPrice,sales from products,category"
				+ " where products.cId=category.cId";
		String sqlNum = "select COUNT(*) as total from products,category" 
				+ " where products.cId=category.cId";
		if(secondcId > 0){
			sqlStr += " and products.cId=" + secondcId;
			sqlNum += " and products.cId=" + secondcId;
		}else if(firstcId > 0){
			sqlStr += " and (category.parentId=" + firstcId + " or products.cId=" + firstcId + ")";
			sqlNum += " and (category.parentId=" + firstcId + " or products.cId=" + firstcId + ")";
		}
		if(searchKey != null && !searchKey.isEmpty()){			
			String str = "%";
			for(int i = 0; i < searchKey.length(); i++){
				str += searchKey.charAt(i) + "%";
			}			
			sqlStr += " and pName like '" + str + "'";
			sqlNum += " and pName like '" + str + "'";
		}
		sqlStr += " and products.status=1 and category.status=1 order by products.time desc limit "+((page-1)*size) +","+size;
		sqlNum += " and products.status=1 and category.status=1";
		
		QueryRunner qRunner = new QueryRunner();
		Map<String,Object> map = qRunner.query(getConn(), sqlNum, new MapHandler());		
		List<Map<String, Object>> list = qRunner.query(getConn(), sqlStr, new MapListHandler());
		list.add(0, map);
		return list;
	}

	
}
